﻿using NORM.Entity;
using NORM.SQLObject;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DevelopAssistant.Core
{
    public class TodoManager
    {
        ///名称：T_Todo
        ///作者：wxdong 
        ///创建时间：2019-08-14 23:02 
        ///功能描述：T_Todo	实体类
        [Serializable]
        [Describe("Table")]
        public class TodoDTO : NORM.Entity.EntityBase
        {
            public TodoDTO()
            {
                TableName = "T_Todo";
                PrimaryKey.Add("ID");
            }

            #region Model
            private int _id;
            /// <summary>
            /// 
            /// </summary>
            public int ID
            {
                set { _id = value; }
                get { return _id; }
            }
            private string _name;
            /// <summary>
            /// 
            /// </summary>
            public string Name
            {
                set { _name = value; }
                get { return _name; }
            }
            private string _title;
            /// <summary>
            /// 
            /// </summary>
            public string Title
            {
                set { _title = value; }
                get { return _title; }
            }
            private int? _timeliness;
            /// <summary>
            /// 
            /// </summary>
            public int? Timeliness
            {
                set { _timeliness = value; }
                get { return _timeliness; }
            }
            private string _yearmonth;
            /// <summary>
            /// 
            /// </summary>
            public string YearMonth
            {
                set { _yearmonth = value; }
                get { return _yearmonth; }
            }
            private DateTime? _datetime;
            /// <summary>
            /// 
            /// </summary>
            public DateTime? DateTime
            {
                set { _datetime = value; }
                get { return _datetime; }
            }
            private string _summary;
            /// <summary>
            /// 
            /// </summary>
            public string Summary
            {
                set { _summary = value; }
                get { return _summary; }
            }
            private string _responsibleperson;
            /// <summary>
            /// 
            /// </summary>
            public string ResponsiblePerson
            {
                set { _responsibleperson = value; }
                get { return _responsibleperson; }
            }
            private string _description;
            /// <summary>
            /// 
            /// </summary>
            public string Description
            {
                set { _description = value; }
                get { return _description; }
            }
            private int? _deletesign;
            /// <summary>
            /// 删除标识 0：未删除 1：已删除
            /// </summary>
            public int? DeleteSign
            {
                set { _deletesign = value; }
                get { return _deletesign; }
            }
            #endregion Model
        }

        public interface ITodoService
        {
            /// <summary>
            /// 
            /// </summary>
            /// <param name="limit">限制记录数</param>
            /// <param name="total">共计事项总数</param>
            /// <returns></returns>
            List<TodoDTO> QueryTodoMonthList(int limit, out int total);

            /// <summary>
            /// 
            /// </summary>
            /// <param name="yearMonth"></param>
            /// <returns></returns>
            List<TodoDTO> QueryTodoDateList(int limit, string yearMonth);
            
            /// <summary>
            /// 
            /// </summary>
            /// <param name="yearMonth"></param>
            /// <param name="dateTime"></param>
            /// <returns></returns>
            List<TodoDTO> QueryTodoDateTimeList(int limit, string yearMonth, string dateTime);

            /// <summary>
            /// 
            /// </summary>
            /// <param name="id"></param>
            /// <returns></returns>
            bool DeleteTodoItem(string id);

            /// <summary>
            /// 
            /// </summary>
            /// <param name="todo"></param>
            /// <returns></returns>
            bool NewTodoItem(TodoDTO todo);

            /// <summary>
            /// 
            /// </summary>
            /// <param name="todo"></param>
            /// <returns></returns>
            bool EditTodoItem(TodoDTO todo);

            /// <summary>
            /// 物理永久删除
            /// </summary>
            /// <param name="id"></param>
            /// <returns></returns>
            bool DeleteTodoItemForever(string id);

            /// <summary>
            /// 
            /// </summary>
            /// <param name="id"></param>
            /// <returns></returns>
            TodoDTO GetTodoItem(string id);

            /// <summary>
            /// 
            /// </summary>
            /// <param name="ResponsibleUser"></param>
            /// <returns></returns>
            List<TodoDTO> GetScheduleTasks(string ResponsibleUser);

            /// <summary>
            /// 分页获取待办数据
            /// </summary>
            /// <param name="strFeilds"></param>
            /// <param name="strWhere"></param>
            /// <param name="strOrder"></param>
            /// <param name="pager"></param>
            /// <returns></returns>
            System.Data.DataTable GetTodoListData(string strFeilds, string strWhere, string strOrder, PageLimit pager);

            /// <summary>
            /// 
            /// </summary>
            /// <param name="list"></param>
            void UpdateTodoDeleteSign(List<TodoDTO> list, int deleteSign);

        }

        public class TodoServiceImpl : ITodoService
        {
            private NORM.DataBase.DataBase GetAdoHelper()
            {
                return NORM.DataBase.DataBaseFactory.Default;
            }

            public List<TodoDTO> QueryTodoMonthList(int limit, out int total)
            {
                using (var db = GetAdoHelper())
                {
                    string sql = @"SELECT YearMonth,COUNT(1) as DateItems 
                                 FROM ( SELECT * 
                                 FROM T_Todo WHERE 1=1 and DeleteSign=0 
                                 ORDER BY DateTime DESC 
                                 LIMIT @limit ) T 
                                 GROUP BY YearMonth 
                                 ORDER BY DateTime DESC; 
                                 SELECT COUNT(1) FROM T_Todo WHERE 1=1 and DeleteSign=0";

                    String sqlText = sql.Replace("@limit", limit.ToString());

                    System.Data.DataSet ds = db.QueryDataSet(System.Data.CommandType.Text, sqlText, null);
                    int records = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                    List<TodoDTO> list = NORM.Common.DTLConvert<TodoDTO>.ToList(ds.Tables[0]);

                    total = records;
                    return list;

                }
            }

            public List<TodoDTO> QueryTodoDateList(int limit, string yearMonth)
            {
                using (var db = GetAdoHelper())
                {
                    string sql = @"SELECT YearMonth,date(DateTime) as [DateTime]
                                 FROM ( SELECT * 
                                 FROM T_Todo WHERE 1=1 and DeleteSign=0 
                                 ORDER BY DateTime DESC 
                                 LIMIT @limit
                                 ) t WHERE 1=1 
                                 and DeleteSign=0 and YearMonth=@YearMonth
                                 GROUP BY date(DateTime)
                                 ORDER BY DateTime DESC ";

                    String sqlText = sql.Replace("@limit", limit.ToString())
                          .Replace("@YearMonth", "'"+ yearMonth + "'");

                    System.Data.DataSet ds = db.QueryDataSet(System.Data.CommandType.Text, sqlText, null);
                    //List<TodoDTO> list = NORM.Common.DTLConvert<TodoDTO>.ToList(ds.Tables[0]);

                    List<TodoDTO> list = new List<TodoDTO>();
                    if(ds!=null && ds.Tables.Count >0)
                    {
                        foreach (System.Data.DataRow row in ds.Tables[0].Rows)
                        {
                            TodoDTO dto = new TodoDTO();                            
                            dto.YearMonth = row["YearMonth"] + "";
                            dto.DateTime = Convert.ToDateTime(row["DateTime"]);
                            list.Add(dto);
                        }
                    }

                    return list;

                }
            }

            public List<TodoDTO> QueryTodoDateTimeList(int limit, string yearMonth,string dateTime)
            {
                using (var db = GetAdoHelper())
                {
                    string sql = @"SELECT *
                                 FROM ( SELECT *                                  
                                 FROM T_Todo WHERE 1=1 and DeleteSign=0 
                                 ORDER BY DateTime DESC 
                                 LIMIT @limit
                                 ) t WHERE 1=1 
                                 and DeleteSign=0 and YearMonth='@YearMonth' 
                                 and DateTime>='@StartDateTime' and DateTime<='@EndDateTime'
                                 ORDER BY [DateTime] DESC ";

                    String sqlText = sql.Replace("@limit", limit.ToString())
                        .Replace("@YearMonth", yearMonth)
                        .Replace("@StartDateTime", Convert.ToDateTime(dateTime).ToString("yyyy-MM-dd 00:00:00"))
                        .Replace("@EndDateTime", Convert.ToDateTime(dateTime).AddDays(1).ToString("yyyy-MM-dd 00:00:00"));

                    System.Data.DataSet ds = db.QueryDataSet(System.Data.CommandType.Text, sqlText, null);                   
                    List<TodoDTO> list = NORM.Common.DTLConvert<TodoDTO>.ToList(ds.Tables[0]);

                    return list;

                }
            }

            public bool DeleteTodoItem(string id)
            {
                bool result = false;
                using (var db = GetAdoHelper())
                {
                    string sql = "update T_Todo set DeleteSign=@DeleteSign where ID=@ID ";

                    sql = sql.Replace("@ID", "'" + id + "'").Replace("@DeleteSign", "1");

                    result = db.Execute(System.Data.CommandType.Text, sql, null) > 0 ? true : false;
                }
                return result;
            }

            public bool NewTodoItem(TodoDTO todo)
            {
                bool success = false;

                using (var db = GetAdoHelper())
                {
                    string sql = @"
INSERT INTO [T_Todo]
(
 [Name],[Title],[Timeliness],[YearMonth],[DateTime],[Summary],[ResponsiblePerson],[Description],[DeleteSign]
)
VALUES
(
 @Name,@Title,@Timeliness,@YearMonth,@DateTime,@Summary,@ResponsiblePerson,@Description,@DeleteSign
)
                            ";

                    todo.YearMonth = Convert.ToDateTime(todo.DateTime).ToString("yyyy-MM");

                    sql = sql.Replace("@Name", "'" + todo.Name + "'")
                        .Replace("@Title", "'" + todo.Title + "'")
                        .Replace("@Timeliness", "'" + todo.Timeliness + "'")
                        .Replace("@YearMonth", "'" + todo.YearMonth + "'")
                        .Replace("@DateTime", "'" + Convert.ToDateTime(todo.DateTime).ToString("yyyy-MM-dd HH:mm:ss") + "'")
                        .Replace("@Summary", "'" + todo.Summary + "'")
                        .Replace("@ResponsiblePerson", "'" + todo.ResponsiblePerson + "'")
                        .Replace("@Description", "'" + todo.Description + "'")
                        .Replace("@DeleteSign", "'" + todo.DeleteSign + "'");

                    int result = db.Execute(System.Data.CommandType.Text, sql, null);

                    success = result > 0 ? true : false;

                }

                return success;
            }

            public bool EditTodoItem(TodoDTO todo)
            {
                bool success = false;

                using (var db = GetAdoHelper())
                {
                    string sql = @"
UPDATE [T_Todo] SET [Name]=@Name , [Title]=@Title , [Summary]=@Summary, [Description]=@Description ,[ResponsiblePerson]=@ResponsiblePerson ,[YearMonth]=@YearMonth ,[DateTime]=@DateTime ,[Timeliness]=@Timeliness  
WHERE [ID]=@ID
                                 ";  //,[Timeliness]=@Timeliness

                    sql = sql.Replace("@ID", "'" + todo.ID + "'")
                       .Replace("@Name", "'" + todo.Name + "'")
                       .Replace("@Title", "'" + todo.Title + "'")
                       .Replace("@Timeliness", "'" + todo.Timeliness + "'")
                       .Replace("@YearMonth", "'" + todo.YearMonth + "'")
                       .Replace("@DateTime", "'" + Convert.ToDateTime(todo.DateTime).ToString("yyyy-MM-dd HH:mm:ss") + "'")
                       .Replace("@Summary", "'" + todo.Summary + "'")
                       .Replace("@ResponsiblePerson", "'" + todo.ResponsiblePerson + "'")
                       .Replace("@Description", "'" + todo.Description + "'")
                       .Replace("@DeleteSign", "'" + todo.DeleteSign + "'");

                    int result = db.Execute(System.Data.CommandType.Text, sql, null);

                    success = result > 0 ? true : false;

                }

                return success;
            }

            public bool DeleteTodoItemForever(string id)
            {

                using (var db = GetAdoHelper())
                {
                    string sql = @"
delete from T_Todo where ID=@ID
";
                    sql = sql.Replace("@ID", id);
                    int result = db.Execute(System.Data.CommandType.Text, sql, null);
                    if (result > 0)
                    {
                        return true;
                    }
                }
                return false;
            }

            public void UpdateTodoDeleteSign(List<TodoDTO> list, int deleteSign)
            {
                using (var db = GetAdoHelper())
                {
                    db.BeginTransaction();

                    try
                    {
                        foreach (var item in list)
                        {
                            string sql = "update T_Todo set DeleteSign=@DeleteSign where ID=@ID ";
                            sql = sql.Replace("@ID", "'" + item.ID + "'").Replace("@DeleteSign", deleteSign.ToString());

                            db.Execute(System.Data.CommandType.Text, sql, null);
                        }
                        db.Commit();
                    }
                    catch (Exception ex)
                    {
                        db.RollBack();
                    }
                }
            }

            public TodoDTO GetTodoItem(string id)
            {
                TodoDTO todoItem = new TodoDTO();

                using (var db = GetAdoHelper())
                {
                    string sql = "select * from t_todo where ID=@ID limit 1 ";
                    sql = sql.Replace("@ID", id);

                    System.Data.DataTable dataTable = db.QueryTable(System.Data.CommandType.Text, sql, null);

                    foreach(System.Data.DataRow row in dataTable.Rows)
                    {
                        if (row != null)
                        {
                            if (row["ID"] != null && row["ID"].ToString() != "")
                            {
                                todoItem.ID = Convert.ToInt32(row["ID"]);
                            }
                            if (row["Name"] != null && row["Name"].ToString() != "")
                            {
                                todoItem.Name = row["Name"].ToString();
                            }
                            if (row["Title"] != null && row["Title"].ToString() != "")
                            {
                                todoItem.Title = row["Title"].ToString();
                            }
                            if (row["Timeliness"] != null && row["Timeliness"].ToString() != "")
                            {
                                todoItem.Timeliness = Convert.ToInt32(row["Timeliness"]);
                            }
                            if (row["YearMonth"] != null && row["YearMonth"].ToString() != "")
                            {
                                todoItem.YearMonth = row["YearMonth"].ToString();
                            }
                            if (row["DateTime"] != null && row["DateTime"].ToString() != "")
                            {
                                todoItem.DateTime = Convert.ToDateTime(row["DateTime"]);
                            }
                            if (row["Summary"] != null && row["Summary"].ToString() != "")
                            {
                                todoItem.Summary = row["Summary"].ToString();
                            }
                            if (row["ResponsiblePerson"] != null && row["ResponsiblePerson"].ToString() != "")
                            {
                                todoItem.ResponsiblePerson = row["ResponsiblePerson"].ToString();
                            }
                            if (row["Description"] != null && row["Description"].ToString() != "")
                            {
                                todoItem.Description = row["Description"].ToString();
                            }
                            if (row["DeleteSign"] != null && row["DeleteSign"].ToString() != "")
                            {
                                todoItem.DeleteSign = Convert.ToInt32(row["DeleteSign"]);
                            }

                        }
                    }

                }

                return todoItem;
            }

            public List<TodoDTO> GetScheduleTasks(string ResponsibleUser)
            {
                List<TodoDTO> list = null;
                using (var db = GetAdoHelper())
                {
                    string sql = "select * from [T_Todo] where DeleteSign=0 and Timeliness<5 and DateTime<'" + DateTime.Now.ToString("yyyy-MM-dd 23:59:59") + "' limit 5";
                    System.Data.DataSet ds = db.QueryDataSet(System.Data.CommandType.Text, sql, null);
                    list = NORM.Common.DTLConvert<TodoDTO>.ToList(ds.Tables[0]);
                }
                return list;
            }

            public System.Data.DataTable GetTodoListData(string strFeilds, string strWhere, string strOrder, PageLimit pager)
            {
                using (var db = GetAdoHelper())
                {
                    string sql = @"
SELECT COUNT(1)
FROM [T_Todo];
SELECT [ID]
    ,[Name]
    ,[Title]
    ,[Timeliness]
    ,(CASE [Timeliness] WHEN 1 THEN '进行中' WHEN 5 THEN '已作废' WHEN 6 THEN '已完成' ELSE '未知' END ) as [TimelinessState]
    ,[YearMonth]
    ,[DateTime]
    ,[Summary]
    ,[ResponsiblePerson]     
    ,[DeleteSign]
FROM [T_Todo]
WHERE @WhereExpress
ORDER BY DateTime DESC
LIMIT @PageSize OFFSET @StartIndex
                    ";

                    int startIndex = (pager.PageIndex - 1) * pager.PageSize;

                    sql = sql.Replace("@WhereExpress", strWhere)
                        .Replace("@PageSize",pager.PageSize.ToString())
                        .Replace("@StartIndex", startIndex.ToString());

                    System.Data.DataSet ds = db.QueryDataSet(System.Data.CommandType.Text, sql, null);
                    System.Data.DataTable dt = ds.Tables[1];
                    pager.RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

                    return dt;
                }
                return null;
            }

        }
    }
}
